﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.IO;
using System.Reflection;
using System.Diagnostics;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;



namespace SQLDeployTool
{
	public class ScriptProcessor
	{
		#region Private fields
		
		private string m_dbName;
		private string m_dbServer;

		#endregion

		#region Public Methods

		public ScriptProcessor(string[] args)
		{
			// grab the values from the config file
			m_dbServer = ConfigurationManager.AppSettings["TrustedServer"];
			m_dbName = ConfigurationManager.AppSettings["TrustedDatabase"];

			Console.Clear();
			Console.ForegroundColor = ConsoleColor.DarkYellow;
			Console.WriteLine("Version: " + getProductVersion() + "\n");
			Console.ResetColor();

			getParameters(args);
		}

		public void ProcessFiles()
		{

			if ( isReadyToDeploy() )
			{
				XmlLogger xlog = new XmlLogger();
				ScriptStatus scriptStatus = new ScriptStatus();
				xlog.Database = m_dbName;
				xlog.Server = m_dbServer;
				

				foreach ( string file in this.getFiles() )
				{

					try
					{

						Console.Write("Processing {0}", file);

						xlog.BeginProcessing = DateTime.Now;
						xlog.ScriptName = file;

						scriptStatus = RunSqlFile(file);

						xlog.ScriptStatus = scriptStatus.Status;
						xlog.ScriptMessage = scriptStatus.Message;
						xlog.EndProcessing = DateTime.Now;
						xlog.Update();
						switch ( xlog.ScriptStatus )
						{
							case XmlLogger.Status.Success:
								Console.ForegroundColor = ConsoleColor.Green;
								break;
							case XmlLogger.Status.Error:
								Console.ForegroundColor = ConsoleColor.Red;
								break;
							default:
								break;
						}
						Console.Write(" - {0}", xlog.ScriptStatus);
						Console.WriteLine();
						Console.ResetColor();
					}

					catch
					{
						throw;
					}

				}
			}
		}
		//TODO: Prompt if values aren't in config file
		//TODO: allow for non-trusted connections
		
		#endregion
		
		#region Private methods
		
		private void getParameters(string[] args)
		{
			if ( args.Length == 0 )
			{
				if ( string.IsNullOrEmpty(m_dbServer) )
				{
					Console.Write("Enter the database server: ");
					m_dbServer = Console.ReadLine();
				}

				if ( string.IsNullOrEmpty(m_dbName) )
				{
					Console.Write("Enter the database name: ");
					m_dbName = Console.ReadLine();
				}
			}
			else
			{
				// grab the command line arguments
				for ( int i = 0; i < args.Length; i++ )
				{
					if ( args[i].ToUpper() == "-S" )
					{
						try
						{
							m_dbServer = args[i + 1];
						}
						catch
						{
							Console.WriteLine("Error! Your parameters are incorrect.");
						}
					}
					if ( args[i].ToUpper() == "-D" )
					{
						try
						{
							m_dbName = args[i + 1];
						}
						catch
						{
							Console.WriteLine("Error! Your parameters are incorrect.");

						}
					}
				}
			}
		}

		/// <summary>
		/// If dbNull and dbName have values and you answered yes to the deploy question, return true.
		/// </summary>
		/// <param name="dbName"></param>
		/// <param name="dbServer"></param>
		/// <returns></returns>
		//public bool isReadyToDeploy(string dbName, string dbServer)
		private bool isReadyToDeploy()
		{
			if ( string.IsNullOrEmpty(m_dbName) || string.IsNullOrEmpty(m_dbServer) )
			{
				Console.WriteLine("Error! Either the database name or the server name is null.");
				return false;
			}
			Console.WriteLine("Type YES to to deploy changes to the {0} database on {1}.", m_dbName, m_dbServer);
			string answer = Console.ReadLine();
			if ( answer.ToUpper() == "YES" )
			{
				return true;
			}
			else
			{
				return false;
			}

		}


		/// <summary>
		/// Reads the file version info from the AssemblyInfo.cs file to display when needed
		/// </summary>
		/// <returns></returns>
		private string getProductVersion()
		{
			Assembly asm = Assembly.GetExecutingAssembly();
			FileVersionInfo fvi = FileVersionInfo.GetVersionInfo(asm.Location);
			return fvi.FileVersion;
		}


		/// <summary>
		/// Execute the .sql files using the SQLCMD.exe
		/// </summary>
		/// <param name="files"></param>
		/// <param name="dbServer"></param>
		private void processFileOld(List<string> files, string dbServer, string dbName)
		{
			XmlLogger xlog = new XmlLogger();
			System.Diagnostics.Process myProcess = new System.Diagnostics.Process();
			System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo("CMD.exe");

			info.RedirectStandardInput = true;
			info.RedirectStandardOutput = true;
			info.UseShellExecute = false;
			info.CreateNoWindow = true;

			myProcess.StartInfo = info;
			myProcess.Start();

			System.IO.StreamWriter SW = myProcess.StandardInput;
			System.IO.StreamReader SR = myProcess.StandardOutput;

			SW.AutoFlush = true;

			//SW.WriteLine("@echo *******Start processing - {0} >> {1}", DateTime.Now.ToString(), logfile);
			//SW.WriteLine("@echo. >> {0}", logfile);

			foreach ( string file in files )
			{

				try
				{
					Console.WriteLine("Processing {0}", file);

					xlog.BeginProcessing = DateTime.Now;
					xlog.ScriptName = file;

					//if (isFileReady(logfile.ToString())) { SW.WriteLine("@echo **** {0} >> {1}", file, logfile); };

					//if (isFileReady(logfile.ToString())) { SW.WriteLine(@"sqlcmd -E -S {1} -d {3} -i {0} >> {2}", file, dbServer, logfile.ToString(),dbName); }
					SW.WriteLine(@"sqlcmd -E -S {1} -d {2} -i {0} ", file, dbServer, dbName);

					xlog.EndProcessing = DateTime.Now;
					xlog.Update();


					//if (isFileReady(logfile.ToString())) { SW.WriteLine("@echo. >> {0}", logfile); }

					SR.Dispose();



				}

				catch
				{
					throw;
				}

			}

			//SW.WriteLine("@echo *******End processing - {0} >> {1}", DateTime.Now.ToString(), logfile);

			//SW.WriteLine(logfile.ToString());
			SW.WriteLine("exit");

			SW.Close();
			SR.Close();


		}

		/// <summary>
		/// Execute the .sql files
		/// </summary>
		/// <param name="files"></param>
		/// <param name="dbServer"></param>
		//public void ProcessFiles(List<string> files, string dbServer, string dbName)
		

		private string getConnectionString()
		{
			string connectionString = String.Format("Server={0};Database={1};Trusted_Connection=True", m_dbServer, m_dbName);
			return connectionString;
		}

		private ScriptStatus RunSqlFile(string file)
		{

			ScriptStatus scriptStatus = new ScriptStatus();
			scriptStatus.Status = XmlLogger.Status.Success;

			SqlConnection conn = new SqlConnection(getConnectionString());
			Server server = new Server(new ServerConnection(conn));

			FileInfo fileSource = new FileInfo(file);
			string script = fileSource.OpenText().ReadToEnd();

			SqlCommand cmd = new SqlCommand();
			cmd.CommandType = System.Data.CommandType.Text;
			cmd.Connection = new SqlConnection(getConnectionString());
			cmd.CommandText = script;

			try
			{
				server.ConnectionContext.ExecuteNonQuery(script);
			}
			catch ( SmoException smoex)
			{
				scriptStatus.Status = XmlLogger.Status.Error;
				scriptStatus.Message = smoex.Message;
			}
			catch ( Exception ex )
			{
				scriptStatus.Status = XmlLogger.Status.Error;
				scriptStatus.Message = ex.InnerException.Message;
			}
			
			return scriptStatus;

		}

		/// <summary>
		/// Check to see if the file is ready to be written to again or if it's already open.
		/// If it's open, wait for 500ms and try again.
		/// </summary>
		/// <param name="file"></param>
		/// <returns></returns>
		[Obsolete]
		protected bool isFileReady(string file)
		{
			//This is no longer needed since log has been changed to xml
			//Obsolete - 12 July 2011
			FileStream fs = null;
			FileInfo f = new FileInfo(file);
			bool isReady = false;

			while ( !isReady )
			{
				try
				{
					fs = f.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
					isReady = true;
				}
				catch
				{
					System.Threading.Thread.Sleep(500);
				}
				finally
				{
					if ( fs != null )
					{
						fs.Close();
					}
				}

			}

			return isReady;

		}

		/// <summary>
		/// Get a list of files in the directory (filtered by .sql extension)
		/// </summary>
		/// <returns></returns>
		private List<string> getFiles()
		{
			System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(".");
			List<string> files = new List<string>();

			// Read the current directory for .sql files
			foreach ( System.IO.FileInfo f in di.GetFiles("*.sql") )
			{
				files.Add(f.Name);
			}

			return files;
		}
		#endregion

		
	}

}
